﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class TitleServiceSummaryRepo
    {
        public const string m_msg_head = "Title ServiceSummary";

        public IEnumerable<TitleServiceSummaryModels> GetTitleServiceSummary()
        {
            List<TitleServiceSummaryModels> TitleServiceSummarys = new List<TitleServiceSummaryModels>();
            string SQLCommand = @"select * from TBTitleServiceSummary WHERE Status <> 'Delete' Order by TitleServiceSummaryId";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        TitleServiceSummaryModels TitleServiceSummary = new TitleServiceSummaryModels();
                        TitleServiceSummary.TitleServiceSummaryId = (int)reader["TitleServiceSummaryId"];
                        TitleServiceSummary.TitleServiceSummaryName = (string)reader["TitleServiceSummaryName"];
                        TitleServiceSummary.Std = (int)reader["Std"];
                        TitleServiceSummary.Status = (string)reader["Status"];

                        TitleServiceSummarys.Add(TitleServiceSummary);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return TitleServiceSummarys.ToList();
        }

        public TitleServiceSummaryModels GetTitleMwwDispenserById(int id)
        {
            TitleServiceSummaryModels TitleServiceSummary = new TitleServiceSummaryModels();
            string SQLCommand = @"select * from TBTitleServiceSummary where TitleServiceSummaryId=@TitleServiceSummaryId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@TitleMwwDispenserId", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        TitleServiceSummary.TitleServiceSummaryId = (int)reader["TitleServiceSummaryId"];
                        TitleServiceSummary.TitleServiceSummaryName = (string)reader["TitleServiceSummaryName"];
                        TitleServiceSummary.Std =(reader["Std"]!=DBNull .Value )? (int)reader["Std"]:0;
                        TitleServiceSummary.Status = (string)reader["Status"];

                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return TitleServiceSummary;
        }

        public bool Create(TitleServiceSummaryModels TitleServiceSummary)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBTitleServiceSummary
                                    values (@TitleMwwDispenser,@Status)";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleMwwDispenserName", TitleServiceSummary.TitleServiceSummaryName);
                    db.AddParameter("@Std", (!String.IsNullOrEmpty(TitleServiceSummary.Std.ToString()))? TitleServiceSummary.Std:0);
                    db.AddParameter("@Status", "Active");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(TitleServiceSummaryModels TitleServiceSummary)
        {
            bool result = true;
            string SQLCommand = @"Update TBTitleServiceSummary 
                                    Set TitleServiceSummaryName=@TitleServiceSummaryName
                                        Std=@Std
                                    where TitleServiceSummaryId=@TitleServiceSummaryId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleServiceSummaryId", TitleServiceSummary.TitleServiceSummaryId);
                    db.AddParameter("@TitleServiceSummaryName", TitleServiceSummary.TitleServiceSummaryName);
                    db.AddParameter("@Std", (!String.IsNullOrEmpty(TitleServiceSummary.Std.ToString())) ? TitleServiceSummary.Std : 0);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(TitleServiceSummaryModels TitleServiceSummary)
        {
            bool result = true;
            string SQLCommand = @"Update TBTitleServiceSummary
                                    Set Status=@Status
                                    where TitleServiceSummaryId=@TitleServiceSummaryId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleServiceSummaryId", TitleServiceSummary.TitleServiceSummaryId);
                    db.AddParameter("@Status", "Delete");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }

    }
}